#!/usr/bin/python
# -*- coding: UTF-8 -*-
"""
Python 3.x
SQLAlchemy 1.4.41

SQLAlchemy 表关系
外键
"""
from sqlalchemy import Column, Integer, String, Text
from constants import DB_URI
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine(DB_URI, echo=True)

# 所有的类都要继承自`declarative_base`这个函数生成的基类
Base = declarative_base(engine)

from sqlalchemy import ForeignKey


class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50), nullable=False)
    content = Column(Text, nullable=False)
    # `RESTRICT`
    aid = Column(Integer, ForeignKey('author.id', ondelete="RESTRICT"))

    # `NO ACTION`
    # aid = Column(Integer, ForeignKey('author.id', ondelete="NO ACTION"))
    # `CASCADE`
    # aid = Column(Integer, ForeignKey('author.id', ondelete="CASCADE"))
    # `SET NULL`
    # aid = Column(Integer, ForeignKey('author.id', ondelete="SET NULL"))

    def __repr__(self):
        return "<Article(id:%s, title:%s, content:%s, aid:%s)>" % (self.id, self.title, self.content, self.aid)


class Author(Base):
    __tablename__ = 'author'
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50), nullable=False)


# 映射到数据库
Base.metadata.drop_all()
Base.metadata.create_all()

Session = sessionmaker(bind=engine)
session = Session()

author = Author(username="YiYe")
session.add(author)
session.flush()  # 刷新Session获取自增主键
article = Article(title="YiYeBiJi", content="YiYeBiJi Content", aid=author.id)
session.add(article)
session.commit()

# `RESTRICT`
try:
    session.query(Author).filter(Author.id == author.id).delete()
    session.commit()
    tmp_article = session.query(Article).all()
    print(tmp_article)
except Exception as e:
    print(e)
# > Traceback (most recent call last):
# > ...
# > pymysql.err.IntegrityError: (1451, 'Cannot delete or update a parent row: a foreign key constraint fails
# (`xxx`.`article`, CONSTRAINT `article_ibfk_1` FOREIGN KEY (`aid`) REFERENCES `author` (`id`))')

# `NO ACTION`
try:
    session.query(Author).filter(Author.id == author.id).delete()
    session.commit()
    tmp_article = session.query(Article).all()
    print(tmp_article)
except Exception as e:
    print(e)
# > Traceback (most recent call last):
# > ...
# > pymysql.err.IntegrityError: (1451, 'Cannot delete or update a parent row: a foreign key constraint fails
# (`xxx`.`article`, CONSTRAINT `article_ibfk_1` FOREIGN KEY (`aid`) REFERENCES `author` (`id`))')

# `CASCADE`
try:
    session.query(Author).filter(Author.id == author.id).delete()
    session.commit()
    tmp_article = session.query(Article).all()
    print(tmp_article)
except Exception as e:
    print(e)
# > []

# `SET NULL`
try:
    session.query(Author).filter(Author.id == author.id).delete()
    session.commit()
    tmp_article = session.query(Article).all()
    print(tmp_article)
except Exception as e:
    print(e)
# > [<Article(id:1, title:YiYeBiJi, content:YiYeBiJi Content, aid:None)>]

session.close()
